package com.hongdee.crm.carConfig.service.impl;

import com.hongdee.common.util.StringUtil;
import com.hongdee.common.util.TokenUtil;
import com.hongdee.crm.Constant;
import com.hongdee.crm.carConfig.dao.ITCrmCarConfDao;
import com.hongdee.crm.carConfig.entity.TCrmCarConf;
import com.hongdee.crm.carConfig.entity.TCrmCarConfDetail;
import com.hongdee.crm.carConfig.entity.TCrmCarConfExcelTemp;
import com.hongdee.crm.carConfig.service.ITcrmCarConfService;
import jdk.nashorn.internal.parser.Token;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import static com.hongdee.crm.Constant.KF_ROLE_ID;
import static com.hongdee.crm.Constant.SA_ROLE_ID;
import static com.hongdee.crm.Constant.XB_ROLE_ID;


/**
 * Created by My on 2017/5/11.
 */
@Service
public class TCrmCarConfServiceImpl implements ITcrmCarConfService {

    @Autowired
    private ITCrmCarConfDao confDao;

    /**
     * 保存车辆分配配置信息
     *
     * @param carConf
     * @param uid
     */
    @Override
    public void saveCarConf(TCrmCarConf carConf, String uid) throws Exception {
        String companyId = TokenUtil.getCompanyId(uid);
        boolean flag = this.checkAddCarConf(carConf.getRoleId(), carConf.getuId(), companyId);
        if (flag) {
            throw new Exception("该用户已经分配过车辆信息！！");
        }
        //如果分配全部时 验证不能有重复的角色权限
        if (carConf.getConfType().equals(Constant.DISTCARTYPE.CAR_ALL)) {
            flag = this.checkRoleCarConf(carConf.getRoleId(), companyId, carConf.getConfType(), "");
            if (flag) {
                throw new Exception("该角色分配类型为全部，不允许分配其它类型！！");
            }
        } else {
            //其它分配方案 可以选择
            if (!carConf.getConfType().equals(Constant.DISTCARTYPE.CAR_WFP)) {
                flag = this.checkRoleCarConf(carConf.getRoleId(), companyId, carConf.getConfType(), Constant.DISTCARTYPE.CAR_WFP);
                if (flag) {
                    throw new Exception("该角色下已存在其它分配类型，请调整后重新分配！！");
                }
            } else {
                //如果选择未分配 查询该角色下有几个未分配 如果有则提示不可在选择
                flag = this.checkRoleCarConfType(carConf.getRoleId(), companyId, Constant.DISTCARTYPE.CAR_WFP);
                if (flag) {
                    throw new Exception("该角色已存在[未分配车辆]的人员，请调整后重新分配！！");
                }
                //判断要分配的角色是否为全部类型
                flag = this.checkRoleCarConfType(carConf.getRoleId(), companyId, Constant.DISTCARTYPE.CAR_ALL);
                if (flag) {
                    throw new Exception("该角色已存在[全部]的人员，不允许分配[未分配车辆]请调整后重新分配！！");
                }
            }
        }
        flag = this.checkUserCarConf(carConf.getuId(), companyId);
        if (flag) {
            throw new Exception("该用户已经分配车辆，请调整后重新分配！！");
        }
        this.saveCarConfToType(carConf, uid);
    }

    /**
     * 判断角色是否存在其它的分配类型
     *
     * @param roleId
     * @param companyId
     * @param type
     * @return
     * @throws Exception
     */
    private boolean checkRoleCarConf(String roleId, String companyId, String type, String otherType) throws Exception {
        StringBuffer sqlStr = new StringBuffer();
        List<String> params = new ArrayList<>();
        sqlStr.append("select * from T_CRM_CAR_CONF where role_id =? and company_id=? and conf_type<>? ");
        params.add(roleId);
        params.add(companyId);
        params.add(type);
        if (!"".equals(otherType)) {
            sqlStr.append(" and conf_type<>? ");
            params.add(otherType);
        }
        List<Map<String, Object>> list = this.confDao.list(sqlStr.toString(), params.toArray());
        if (list.size() > 0) {
            return true;
        }
        return false;
    }

    /**
     * 判断是否有指定类型的车辆
     *
     * @param roleId
     * @param companyId
     * @param type
     * @return
     * @throws Exception
     */
    private boolean checkRoleCarConfType(String roleId, String companyId, String type) throws Exception {
        StringBuffer sqlStr = new StringBuffer();
        List<String> params = new ArrayList<>();
        sqlStr.append("select * from T_CRM_CAR_CONF where role_id =? and company_id=? and conf_type=? ");
        params.add(roleId);
        params.add(companyId);
        params.add(type);
        List<Map<String, Object>> list = this.confDao.list(sqlStr.toString(), params.toArray());
        if (list.size() > 0) {
            return true;
        }
        return false;
    }


    /**
     * 判断用户是否已经分配了其它规则
     *
     * @param userId
     * @param companyId
     * @return
     * @throws Exception
     */
    private boolean checkUserCarConf(String userId, String companyId) throws Exception {
        String sql = "select * from T_CRM_CAR_CONF where u_id=? and company_id=?";
        List<Map<String, Object>> list = this.confDao.list(sql, userId, companyId);
        if (list.size() > 0) {
            return true;
        }
        return false;
    }

    /**
     * 保存分配规则，并分配车辆信息
     *
     * @param carConf
     * @param uid
     * @throws Exception
     */
    private void saveCarConfToType(TCrmCarConf carConf, String uid) throws Exception {
        String companyId = TokenUtil.getCompanyId(uid);
        switch (carConf.getConfType()) {
            case Constant.DISTCARTYPE.CAR_ALL:
                //选择全部 直接保存
//                this.saveCrmCar(carConf,uid);
                break;
            case Constant.DISTCARTYPE.CAR_WH:
                //1、查看已经分配的数值是否有人已经分配
                boolean flag = this.checkPlateNum(carConf.getConfType(), carConf.getConfVal(), carConf.getRoleId(), "", companyId);
                //2、没有分配保存车辆分配规则
                if (flag) {
                    throw new Exception("该人员分配的车牌号已经存在，请调整后重新分配！！");
                }
                //车辆尾号
//                this.distCarByPlateNum(carConf,uid);
                break;
            case Constant.DISTCARTYPE.CAR_WS:
                boolean wsFlag = this.checkPlateNum(carConf.getConfType(), carConf.getConfVal(), carConf.getRoleId(), "", companyId);
                //2、没有分配保存车辆分配规则
                if (wsFlag) {
                    throw new Exception("该人员分配的车牌号已经存在，请调整后重新分配！！");
                }
                //车辆尾数
//                this.distCarByPlateNum(carConf,uid);
                break;
            case Constant.DISTCARTYPE.CAR_VIN:
//                this.disCarByVin(file,carConf,uid);
                break;
            case Constant.DISTCARTYPE.CAR_CX:
                //暂时不操作
                break;
            case Constant.DISTCARTYPE.CAR_WFP:
                //未分配
//                this.disCarByWfp(carConf,uid);
                break;
        }

        this.saveCrmCar(carConf, uid);

        distCarByCarConf(carConf,uid,null,"0");

    }

    /**
     * 修改车辆分配规则
     *
     * @param carConf
     * @param uid
     * @throws Exception
     */
    @Override
    public void editCarConf(TCrmCarConf carConf, String uid) throws Exception {
        String companyId = TokenUtil.getCompanyId(uid);
        //验证是否有其它的
        switch (carConf.getConfType()) {
            case Constant.DISTCARTYPE.CAR_ALL:
                //选择全部 直接保存
//                this.saveCrmCar(carConf,uid);
                break;
            case Constant.DISTCARTYPE.CAR_WH:
                //1、查看已经分配的数值是否有人已经分配
                boolean flag = this.checkPlateNum(carConf.getConfType(), carConf.getConfVal(), carConf.getRoleId(), carConf.getuId(), companyId);
                //2、没有分配保存车辆分配规则
                if (flag) {
                    throw new Exception("该人员分配的车牌号已经存在，请调整后重新分配！！");
                }
                //车辆尾号
//                this.distCarByPlateNum(carConf,uid);
                break;
            case Constant.DISTCARTYPE.CAR_WS:
                boolean wsFlag = this.checkPlateNum(carConf.getConfType(), carConf.getConfVal(), carConf.getRoleId(), carConf.getuId(), companyId);
                //2、没有分配保存车辆分配规则
                if (wsFlag) {
                    throw new Exception("该人员分配的车牌号已经存在，请调整后重新分配！！");
                }
                //车辆尾数
//                this.distCarByPlateNum(carConf,uid);
                break;
            case Constant.DISTCARTYPE.CAR_VIN:
//                this.disCarByVin(file,carConf,uid);
                break;
            case Constant.DISTCARTYPE.CAR_CX:
                //暂时不操作
                break;
            case Constant.DISTCARTYPE.CAR_WFP:
                //未分配
//                this.disCarByWfp(carConf,uid);
                break;
        }
        this.confDao.saveOrUpdate(carConf);
    }


    /**
     * 根据车辆分配规则，进行分配车辆
     *
     * @param confId
     * @param uid
     * @param file
     * @throws Exception
     */
    @Override
    public void distCarByCarConf(String confId, String uid, MultipartFile file, String type) throws Exception {
        String companyId = TokenUtil.getCompanyId(uid);
        TCrmCarConf carConf = this.confDao.getCrmCarConfById(confId, companyId);
        if (carConf != null) {
            this.distCarByCarConf(carConf, uid, file, type);
        } else {
            throw new Exception("未查询到车辆分配规则！");
        }
        updCrmCarSaId(companyId);
    }

    private void updCrmCarSaId(String companyId) throws Exception {

        //刷新 SA_ID
        StringBuffer sql = new StringBuffer();
        sql.append(" update  t_crm_car car,t_crm_car_conf_detail detail set car.sa_id=detail.u_id where car.vin=detail.vin and car.company_id=detail.company_id " +
                "and detail.company_id=? and detail.role_id=? and car.company_id=?");
        this.confDao.executeSQL(sql.toString(), companyId, SA_ROLE_ID, companyId);
        //刷新 客服 ID
        sql = new StringBuffer();
        sql.append(" update  t_crm_car car,t_crm_car_conf_detail detail set car.kf_id=detail.u_id where car.vin=detail.vin and car.company_id=detail.company_id " +
                "and detail.company_id=? and detail.role_id=? and car.company_id=?");
        this.confDao.executeSQL(sql.toString(), companyId, KF_ROLE_ID, companyId);
        // 刷新 续保 ID
        sql = new StringBuffer();
        sql.append(" update  t_crm_car car,t_crm_car_conf_detail detail set car.bx_id=detail.u_id where car.vin=detail.vin and car.company_id=detail.company_id " +
                "and detail.company_id=? and detail.role_id=? and car.company_id=?");
        this.confDao.executeSQL(sql.toString(), companyId, XB_ROLE_ID, companyId);
    }

    /**
     * 1、仅限尾号、尾数、未分配信息
     * 2、查询除VIN以外的分配方式；
     * 3、根据分配方式，先将分配信息清除
     * 4、在根据分配方式，将信息重新分配
     *
     * @param uid
     */
    @Override
    public void distCarByAllCarConf(String uid) throws Exception {
        String companyId = TokenUtil.getCompanyId(uid);
        List<TCrmCarConf> crmCarConfs = confDao.getCrmCarConfByNotVin(uid);
        //清除
        String cleanSql = "delete from t_crm_car_conf_detail where u_id in (select u_id from T_CRM_CAR_CONF where conf_type>3 " +
                "or conf_type<3 and company_id=?)";
        this.confDao.executeSQL(cleanSql, companyId);
        //重新分配
        for (TCrmCarConf crmCarConf : crmCarConfs) {
            distCarByCarConf(crmCarConf, uid, null, "0");
        }
    }


    /**
     * 保存分配规则，并分配车辆信息
     *
     * @param carConf
     * @param uid
     * @throws Exception
     */
    private void distCarByCarConf(TCrmCarConf carConf, String uid, MultipartFile file, String type) throws Exception {
        switch (carConf.getConfType()) {
            case Constant.DISTCARTYPE.CAR_ALL:
                //选择全部 直接保存
//                this.saveCrmCar(carConf,uid);
                break;
            case Constant.DISTCARTYPE.CAR_WH:
                //车辆尾号
                this.distCarByPlateNum(carConf, uid, type);
                break;
            case Constant.DISTCARTYPE.CAR_WS:
                //车辆尾数
                this.distCarByPlateNum(carConf, uid, type);
                break;
            case Constant.DISTCARTYPE.CAR_VIN:
                this.disCarByVin(file, carConf, uid, type);
                break;
            case Constant.DISTCARTYPE.CAR_CX:
                //暂时不操作
                break;
            case Constant.DISTCARTYPE.CAR_WFP:
                //未分配
                this.disCarByWfp(carConf, uid);
                break;
        }
    }

    /**
     * 删除分配的车辆信息
     * 同时清除车辆表中对应的人员信息
     * SA、续保、客服
     *
     * @param carConf
     * @throws Exception
     */
    private void delCarCarConf(TCrmCarConf carConf) throws Exception {
        String sql = "delete from t_crm_car_conf_detail where role_id=? and u_id=?";
        this.confDao.executeSQL(sql, carConf.getRoleId(), carConf.getuId());
        String roleId = String.valueOf(carConf.getRoleId());
        String kfSQL = " update t_crm_car set kf_id = null where kf_id = ? ";
        String saSQL = " update t_crm_car set sa_id = null where sa_id = ? ";
        String xbSQL = " update t_crm_car set bx_id = null where bx_id = ? ";
        //清空车辆对应字段的值
        switch (roleId) {
            case SA_ROLE_ID:
                this.confDao.executeSQL(saSQL, carConf.getuId());
                break;
            case XB_ROLE_ID:
                this.confDao.executeSQL(xbSQL, carConf.getuId());
                break;
            case KF_ROLE_ID:
                this.confDao.executeSQL(kfSQL, carConf.getuId());
                break;
            default:

        }

    }


    //---------------------------------车辆VIN分配开始---------------------------

    private void disCarByVin(MultipartFile file, TCrmCarConf carConf, String uid, String type) throws Exception {
        if ("0".equals(type)) {
            this.delCarCarConf(carConf);
        }
        //1、保存车辆分配规则
//        carConf=this.saveCrmCar(carConf,uid);
        //2、解析Vin资源
        String fileName = file.getOriginalFilename();
        String fileType = fileName.substring(fileName.lastIndexOf("."));
        if (fileType != null && (fileType.endsWith("xlsx") || fileType.endsWith("xls"))) {
            List<TCrmCarConfExcelTemp> excelTemps = this.readExcel(file, uid, carConf.getId());
            //分配全部时 先删除本次请求的VIN资源信息
            if ("0".equals(type)) {
                //3、删除该分配规则资源下的VIn号
                this.delExcelTempByConf(carConf);
            }
            //4、保存分配的Vin资源
            this.confDao.saveOrUpdateList(excelTemps);
            //5、插入分配详细
            this.saveVinDetail(carConf, type);
            //6、删除本次VIN分配明细资源
            this.delExcelTempByConf(carConf);
        } else {
            throw new Exception("无法识别的文件格式！");
        }
    }

    /**
     * 分配VIN车辆资源
     *
     * @param carConf
     * @throws Exception
     */
    private void saveVinDetail(TCrmCarConf carConf, String type) throws Exception {
        StringBuffer sqlStr = new StringBuffer();
        List<String> params = new ArrayList<>();
        sqlStr.append(" insert into t_crm_car_conf_detail(id,company_id,role_id,u_id,vin) ");
        sqlStr.append(" select REPLACE(id,'-','') id,'" + carConf.getCompanyId() + "','" + carConf.getRoleId() + "' ,'" + carConf.getuId() + "',vin from ( ");
        sqlStr.append(" select UUID() id,vin from t_crm_car c where c.company_id=? ");
        params.add(carConf.getCompanyId());
        sqlStr.append(" AND EXISTS(select * from t_crm_car_conf_excel_temp where vin=c.vin and config_id=?) ");
        params.add(carConf.getId());
        //0分配全部 1 分配未分配的车辆
        if (!"0".equals(type)) {
            sqlStr.append(" AND not EXISTS (select * from t_crm_car_conf_detail where role_id=? and company_id=? and vin=c.vin) ");
            params.add(carConf.getRoleId());
            params.add(carConf.getCompanyId());
        }
        sqlStr.append(")t");
        this.confDao.executeSQL(sqlStr.toString(), params.toArray());
    }

    /**
     * 删除分配的VIN资源信息
     *
     * @param carConf
     * @throws Exception
     */
    private void delExcelTempByConf(TCrmCarConf carConf) throws Exception {
        String sql = "delete from T_CRM_CAR_CONF_EXCEL_TEMP where config_id=?";
        this.confDao.executeSQL(sql, carConf.getId());
    }

    /**
     * 读取xls文件内容
     *
     * @return List<XlsDto>对象
     * @throws IOException 输入/输出(i/o)异常
     */
    @Override
    public List<TCrmCarConfExcelTemp> readExcel(MultipartFile file, String uid, String configId) throws IOException {
        InputStream is = file.getInputStream();
        Workbook book = null;
        try {
            book = new XSSFWorkbook(is);
        } catch (Exception ex) {
            book = new HSSFWorkbook(is);
        }
        List<TCrmCarConfExcelTemp> list = new ArrayList<TCrmCarConfExcelTemp>();
        // 循环工作表Sheet
        for (int numSheet = 0; numSheet < book.getNumberOfSheets(); numSheet++) {
            Sheet sheet = book.getSheetAt(numSheet);
            if (sheet == null) {
                continue;
            }
            // 循环行Row
            for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
                Row row = sheet.getRow(rowNum);
                if (row == null) {
                    continue;
                }
                TCrmCarConfExcelTemp excemTemp = new TCrmCarConfExcelTemp();
                // 循环列Cell
                Cell xh = row.getCell(0);
                if (xh == null) {
                    continue;
                }
                String vin = getValue(xh);
                if ("".equals(vin)) {
                    continue;
                }
                excemTemp.setVin(vin);
                excemTemp.setCreateTime(new Date().getTime());
                excemTemp.setCreateUser(uid);
                excemTemp.setConfigId(configId);
                list.add(excemTemp);
            }
        }
        return list;
    }


    /**
     * 得到Excel表中的值
     *
     * @param cell Excel中的每一个格子
     * @return Excel中每一个格子中的值
     */
    @SuppressWarnings("static-access")
    private String getValue(Cell cell) {
        if (cell.getCellType() == cell.CELL_TYPE_BOOLEAN) {
            // 返回布尔类型的值
            return String.valueOf(cell.getBooleanCellValue());
        } else if (cell.getCellType() == cell.CELL_TYPE_NUMERIC) {
            // 返回数值类型的值
            return String.valueOf(cell.getNumericCellValue());
        } else {
            // 返回字符串类型的值
            return String.valueOf(cell.getStringCellValue());
        }
    }

    //---------------------------------车辆VIN分配结束---------------------------
    //---------------------------------车辆未分配开始---------------------------
    private void disCarByWfp(TCrmCarConf conf, String uid) throws Exception {
        //1、保存车辆分配信息
//        conf=this.saveCrmCar(conf,uid);
        //2、根据分配规则明细中的角色信息、车辆信息查询出所有未分配的车辆
        StringBuffer sqlStr = new StringBuffer();
        List<String> params = new ArrayList<>();
        sqlStr.append(" insert into t_crm_car_conf_detail(id,company_id,role_id,u_id,vin) ");
        sqlStr.append(" select REPLACE(id,'-','') id,'" + conf.getCompanyId() + "','" + conf.getRoleId() + "' ,'" + conf.getuId() + "',vin from ( ");
        sqlStr.append(" select UUID() id,vin from t_crm_car c where c.company_id=? ");
        params.add(conf.getCompanyId());
        sqlStr.append(" AND NOT EXISTS(select * from t_crm_car_conf_detail where car.vin=vin and role_id=? and company_id=?) ");
        params.add(conf.getRoleId());
        params.add(conf.getCompanyId());
        this.confDao.executeSQL(sqlStr.toString(), params.toArray());
    }

    //---------------------------------车辆未分配结束---------------------------

    //---------------------------------车辆尾号、尾数分配开始---------------------------

    private void distCarByPlateNum(TCrmCarConf conf, String uid, String type) throws Exception {
        //
        if ("0".equals(type)) {
            this.delCarCarConf(conf);
        }
        //1、查看已经分配的数值是否有人已经分配
//        boolean flag=this.checkPlateNum(conf.getConfType(),conf.getConfVal(),conf.getRoleId(),"",TokenUtil.getCompanyId(uid));
//        //2、没有分配保存车辆分配规则
//        if(flag){
//            throw new Exception("该人员分配的车牌号已经存在，请调整后重新分配！！");
//        }
        //3、保存车辆分配信息
//        conf=this.saveCrmCar(conf,uid);
        //4、查询车辆信息保存至车辆分配明细表
        this.savePlateNumDetail(conf, type);
    }

    /**
     * 根据车辆分配规则 分配其所选信息
     *
     * @param conf
     * @throws Exception
     */
    public void savePlateNumDetail(TCrmCarConf conf, String type) throws Exception {
        //车辆分配值，车属号 或车尾数，vin时 另行处理
        String refValue = conf.getConfVal().replaceAll(",", "");
        //根据类型取车辆
        StringBuffer sqlStr = new StringBuffer();
        List<String> params = new ArrayList<>();
        sqlStr.append(" insert into t_crm_car_conf_detail(id,company_id,role_id,u_id,vin) ");
        sqlStr.append(" select REPLACE(id,'-','') id,'" + conf.getCompanyId() + "','" + conf.getRoleId() + "' ,'" + conf.getuId() + "',vin from ( ");
        sqlStr.append(" select UUID() id,vin from t_crm_car c where c.company_id=? ");
        params.add(conf.getCompanyId());
        switch (conf.getConfType()) {
            case Constant.DISTCARTYPE.CAR_WH:
                sqlStr.append(" AND  c.plate_num REGEXP ? ");
                params.add("[" + refValue + "]$");
                break;
            case Constant.DISTCARTYPE.CAR_WS:
                //先不写
                sqlStr.append(" AND c.plate_num REGEXP ? ");
                params.add(".*[" + refValue + "][A-Za-z]*$");
                //根据尾数查询
                break;
        }
        //0全部 1 未分配车辆
        if (!"0".equals(type)) {
            sqlStr.append(" AND NOT EXISTS(select * from t_crm_car_conf_detail where c.vin=vin and role_id=? and company_id=?) ");
            params.add(conf.getRoleId());
            params.add(conf.getCompanyId());
        }
        sqlStr.append(" )t ");
        int i = this.confDao.executeSQL(sqlStr.toString(), params.toArray());

        //变更车辆信息
        /***** 注销*** 改为统一处理**/
        /****
         String upSQL = "";
         switch (conf.getRoleId()) {
         case SA_ROLE_ID:
         upSQL = " update t_crm_car set sa_id = null where sa_id = ? ";
         break;
         case KF_ROLE_ID:
         upSQL = " update t_crm_car set kf_id = null where kf_id = ? ";
         break;
         case XB_ROLE_ID:
         upSQL = " update t_crm_car set xb_id = null where xb_id = ? ";
         break;
         default:
         }
         // 分配3个人的语句和参数
         List tParam = new ArrayList();
         if (upSQL.length() > 0) {
         tParam.add(conf.getuId());
         switch (conf.getConfType()) {
         case Constant.DISTCARTYPE.CAR_WH:
         upSQL += " AND  plate_num REGEXP ? ";
         tParam.add("[" + refValue + "]$");
         break;
         case Constant.DISTCARTYPE.CAR_WS:
         //先不写
         upSQL += " AND plate_num REGEXP ? ";
         tParam.add(".*[" + refValue + "][A-Za-z]*$");
         //根据尾数查询
         break;
         }
         }
         // 如果存在两个参数，进行更新
         if (tParam.size() == 2) {
         this.confDao.executeSQL(upSQL, tParam.toArray());
         }

         */
    }

    /**
     * 检测该角色下是否已经有分配过的车牌尾号或尾数
     *
     * @param refType
     * @param refValue
     * @param roleId
     * @return
     * @throws Exception
     */
    public boolean checkPlateNum(String refType, String refValue, String roleId, String userId, String companyId) throws Exception {
        StringBuffer sqlStr = new StringBuffer();
        List<String> params = new ArrayList<>();
        sqlStr.append(" select * from  t_crm_car_conf where role_id=? and company_id=? and conf_type=? ");
        params.add(roleId);
        params.add(companyId);
        params.add(refType);
        if (!"".equals(userId)) {
            sqlStr.append(" and u_id<>? ");
            params.add(userId);
        }
        sqlStr.append(" and ( ");
        String[] refValues = refValue.split(",");
        for (int i = 0; i < refValues.length; i++) {
            sqlStr.append(" find_in_set(?,conf_val) ");
            params.add(refValues[i]);
            if (i < (refValues.length - 1)) {
                sqlStr.append(" or ");
            }
        }
        sqlStr.append(" ) ");
        List list = this.confDao.list(sqlStr.toString(), params.toArray());
        if (list.size() > 0) {
            return true;
        }
        return false;
    }

    //---------------------------------车辆尾号、尾数分配结束---------------------------

    /**
     * 保存车辆信息
     *
     * @param carConf
     * @param uid
     * @return
     * @throws Exception
     */
    private TCrmCarConf saveCrmCar(TCrmCarConf carConf, String uid) throws Exception {
        carConf.setCompanyId(TokenUtil.getCompanyId(uid));
        carConf.setCreateTime(new Date().getTime());
        carConf.setCreateUser(uid);
        return (TCrmCarConf) this.confDao.saveOrUpdate(carConf);
    }

    @Override
    public boolean checkAddCarConf(String roleId, String saId, String companyId) throws Exception {
        String sql = "select * from t_crm_car_conf where role_id=? and company_id=? and u_id=?";
        List list = confDao.list(sql, roleId, companyId, saId);
        if (list.size() > 0) {
            return true;
        }
        return false;
    }

    /**
     * 获得分配规则详情
     *
     * @param id
     * @param uid
     * @return
     * @throws Exception
     */
    @Override
    public TCrmCarConf getCarConfigById(String id, String uid) throws Exception {
        String companyId = TokenUtil.getCompanyId(uid);
        return confDao.getCrmCarConfById(id, companyId);
    }

    @Override
    public Map carPage(String start, String limit, String roleId, String userId, String type,
                       String plateNum, String vin, String uid) throws Exception {
        String companyId = TokenUtil.getCompanyId(uid);
        StringBuffer sqlStr = new StringBuffer();
        List<String> params = new ArrayList<>();
        sqlStr.append("SELECT t.id,t.role_id,t.u_id,car.vin,car.id carId,car.plate_num,t.u_name,r.r_name FROM t_crm_car car left join (");
        sqlStr.append("select detail.*,u.u_name from t_crm_car_conf_detail detail,t_bc_user u where detail.u_id=u.id ");
        sqlStr.append(" and detail.company_id=? ");
        params.add(companyId);
        if (!StringUtils.isEmpty(roleId)) {
            sqlStr.append(" and detail.role_id=? ");
            params.add(roleId);
        }
        if (!StringUtils.isEmpty(userId)) {
            sqlStr.append(" and detail.u_id=? ");
            params.add(userId);
        }
        sqlStr.append(" ) t  on car.vin=t.vin ");
        sqlStr.append("left JOIN t_bc_role r ON t.role_id=r.id");
        sqlStr.append(" where car.company_id=?");
        params.add(companyId);
        if (!StringUtils.isEmpty(type)) {
            //已分配
            if ("1".equals(type)) {
                sqlStr.append(" and t.id is not null ");
            }
            //未分配
            if ("0".equals(type)) {
                sqlStr.append(" and t.id is null ");
            }
        }
        if (!StringUtils.isEmpty(plateNum)) {
            sqlStr.append(" and car.plate_num like ? ");
            params.add("%" + plateNum + "%");
        }
        if (!StringUtils.isEmpty(vin)) {
            sqlStr.append(" and car.vin like ? ");
            params.add("%" + vin + "%");
        }
        sqlStr.append(" order by t.id DESC ");
        return this.confDao.page(sqlStr.toString(), params.toArray(), start, limit);
    }

    /**
     * 车辆指定分配
     *
     * @param data
     * @param roleId
     * @param uid
     * @throws Exception
     */
    @Override
    public void carReset(List<Map> data, String roleId, String uid, String userid) throws Exception {
        StringBuffer sqlStr = new StringBuffer();
        List<String> params = new ArrayList<>();
        List<TCrmCarConfDetail> detailList = new ArrayList<>();
        String companyId = TokenUtil.getCompanyId(uid);
        sqlStr.append(" delete from T_CRM_CAR_CONF_DETAIL where role_id=? and vin in ( ");
        params.add(roleId);
        for (int i = 0; i < data.size(); i++) {
            Map map = data.get(i);
            String vin = String.valueOf(map.get("vin"));
            sqlStr.append("?");
            params.add(vin);
            if (i < (data.size() - 1)) {
                sqlStr.append(",");
            }
            TCrmCarConfDetail detail = new TCrmCarConfDetail();
            detail.setCompanyId(companyId);
            detail.setRoleId(roleId);
            detail.setuId(userid);
            detail.setVin(vin);
            detailList.add(detail);
        }
        sqlStr.append(")");
        this.confDao.executeSQL(sqlStr.toString(), params.toArray());
        this.confDao.saveOrUpdateList(detailList);
    }

    /**
     * 车辆分配规则查询
     *
     * @param roleId
     * @param userId
     * @param start
     * @param limit
     * @param uid
     * @return
     */
    @Override
    public Map page(String roleId, String userId, String start, String limit, String uid) throws Exception {
        StringBuffer sqlStr = new StringBuffer();
        List<String> params = new ArrayList<>();
        String companyId = TokenUtil.getCompanyId(uid);
        sqlStr.append("select t.*,r.r_name from (");
        sqlStr.append(" select conf.*,u_name from  T_CRM_CAR_CONF conf,T_BC_USER u where conf.u_id=u.id");
        sqlStr.append(" and conf.company_id=? ");
        params.add(companyId);
        if (!StringUtils.isEmpty(userId)) {
            sqlStr.append(" and conf.u_id=? ");
            params.add(userId);
        }
        if (!StringUtils.isEmpty(roleId)) {
            sqlStr.append("and conf.role_id=?");
            params.add(roleId);
        }
        sqlStr.append(") t left join t_bc_role r ON t.role_id=r.id");
        return this.confDao.page(sqlStr.toString(), params.toArray(), start, limit);
    }

    /**
     * 删除车辆分配信息
     *
     * @param configId
     * @param uid
     * @throws Exception
     */
    @Override
    public void removeCarConf(String configId, String uid) throws Exception {
        TCrmCarConf carConf = this.getCarConfigById(configId, uid);
        String sql = "delete from T_CRM_CAR_CONF where id=? and company_id=?";
        this.confDao.executeSQL(sql, carConf.getId(), carConf.getCompanyId());
        this.delCarCarConf(carConf);
    }

    /**
     * 根据车辆配置生成查询sql
     * 为了切换SA增加companyId属性，由调用者传入，不在由此方法获取
     *
     * @param uid
     * @param sqlStr
     * @param byName
     * @throws Exception
     */
    @Override
    public void crmCarConfToSql(String uid, StringBuffer sqlStr, String byName, String companyId) throws Exception {
        //String companyId=TokenUtil.getCompanyId(uid);
        List<String> companys = new ArrayList<>();
        companys.add("8a1029015b612d40015b6142c2a50470");
        companys.add("ff8081815c820561015cc88e49834e0b");
        if (!companys.contains(companyId)) {
            //获得用户的车辆分配配置信息
            TCrmCarConf tCrmCarConf = confDao.getCrmCarConfByUid(uid, companyId);
            if (tCrmCarConf != null) {
                //如果为全部 则不需要关联分配明细表查询
                if (!tCrmCarConf.getConfType().equals(Constant.DISTCARTYPE.CAR_ALL)) {
                    if (tCrmCarConf.getConfType().equals(Constant.DISTCARTYPE.CAR_WFP)) {
                        sqlStr.append(" AND NOT EXISTS( select 1 from t_crm_car_conf_detail where " + byName + ".vin=vin and role_id='" + tCrmCarConf.getRoleId() + "' ) ");
                    } else {
                        sqlStr.append(" AND EXISTS( select 1 from t_crm_car_conf_detail where " + byName + ".vin=vin and u_id='" + uid + "' ) ");
                    }
                }
            }
        }
    }

    @Override
    public List<TCrmCarConf> getCrmCarConfByRoleId(String roleId, String companyId) throws Exception {
        return this.confDao.getCrmCarConfByRoleID(roleId, companyId);

    }


    /**
     * 删除指定人员的车辆资源配置信息
     *
     * @param uid
     * @param data
     * @throws Exception
     */
    @Override
    public void emptyCarConfDetailByUid(String uid, List<Map> data) throws Exception {
        if (data.size() > 0) {
            StringBuffer sql = new StringBuffer();
            List<Object> params = new ArrayList<>();
            sql.append(" delete from t_crm_car_conf_detail where company_id=? ");
            params.add(TokenUtil.getCompanyId(uid));
            sql.append(" and u_id in (");
            for (int i = 0; i < data.size(); i++) {
                Map cnfInfo = data.get(i);
                String uId = StringUtil.formatNull(cnfInfo.get("uId"));
                sql.append("?");
                params.add(uId);
                if (i < (data.size() - 1)) {
                    sql.append(",");
                }
            }
            sql.append(" ) ");
            this.confDao.executeSQL(sql.toString(), params.toArray());
        } else {
            throw new Exception("请选择要清空的人员");
        }
    }

}
